﻿using System;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic; 
using System.Data;
using TimeTravel.Components;
using TimeTravel.Extentions;
using TimeTravel.IData;

namespace TimeTravel.SqlServer  
{
    public class ProductData : IProductData
    {
        #region Fields

        private static readonly SqlHelper DbHelper = new SqlHelper();
        private static readonly string[] Columns = { "[id]", "[name]", "[descr]", "[sort]","[picture]","[click]","[detail]" };

        #endregion

        #region Implementation of IDataBase<ProductInfo>

        /// <summary>
        /// 添加记录
        /// </summary>
        public int Add(ProductInfo product)
        {
            if (product == null) return 0;
            var strSql = new StringBuilder();
            strSql.Append("insert into Product(");
            strSql.Append("[name],[descr],[sort],[picture],[click],[detail]) ");
            strSql.Append(" values ");
            strSql.Append("(@name,@descr,@sort,@picture,@click,@detail );");
            strSql.Append("select @@IDENTITY;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@name", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@descr", SqlDbType.NVarChar, 200),
                                            new SqlParameter("@sort", SqlDbType.Int),
                                            new SqlParameter("@click", SqlDbType.Int),
                                            new SqlParameter("@picture", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@detail", SqlDbType.NVarChar, 1000)
                                        };
            parameters[0].Value = product.Name;
            parameters[1].Value = product.Descr;
            parameters[2].Value = product.Sort;
            parameters[3].Value = product.Click;
            parameters[4].Value = product.Picture;
            parameters[5].Value = product.Detail;
            var result = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString(), parameters);
            return result == null ? 0 : Convert.ToInt32(result);
        }

        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            if (id == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("update Product set DeleteFlag = 1 ");
            strSql.Append("where [id] = @id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="product"></param>
        public void Update(ProductInfo product)
        {
            if (product == null || product.ID == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("update Product set ");
            strSql.Append("[name] = @name,");
            strSql.Append("[descr] = @descr,");
            strSql.Append("[sort] = @sort,");
            strSql.Append("[picture] = @picture,");
            strSql.Append("[detail] = @detail ");
            strSql.Append("where [id] = @id ;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@name", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@descr", SqlDbType.NVarChar, 200),
                                            new SqlParameter("@sort", SqlDbType.Int),
                                            new SqlParameter("@click", SqlDbType.Int),
                                            new SqlParameter("@picture", SqlDbType.NVarChar, 50),
                                            new SqlParameter("@detail", SqlDbType.NVarChar, 1000),
                                            new SqlParameter("@id",SqlDbType.Int)
                                        };
            parameters[0].Value = product.Name;
            parameters[1].Value = product.Descr;
            parameters[2].Value = product.Sort;
            parameters[3].Value = product.Click;
            parameters[4].Value = product.Picture;
            parameters[5].Value = product.Detail;
            parameters[6].Value = product.ID;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取所有
        /// </summary>
        /// <returns></returns>
        public IList<ProductInfo> GetAll()
        {
            var strSql = new StringBuilder();
            strSql.AppendFormat("select {0} from Product ",String.Join(",",Columns));
            strSql.Append("where DeleteFlag = 0;");
            var result = DbHelper.ExecuteObjectList(strSql.ToString(), DataReaderParser);
            return result.ToList(); 
        }

        /// <summary>
        /// 根据条件获取
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public RecordSet<ProductInfo> GetBySearch(PageQueryInfo query)
        {
            if(query == null) return new RecordSet<ProductInfo>();
            return DbHelper.ExecutePaging("Product", query, DataReaderParser, Columns);
        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ProductInfo GetOne(int id)
        {
            if (id == 0) return null;
            var strSql = new StringBuilder();
            strSql.AppendFormat("select {0} from Product ", String.Join(",", Columns));
            strSql.Append("where [id] = @id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            return DbHelper.ExecuteObject(CommandType.Text, strSql.ToString(), DataReaderParser, parameters);
        }

        /// <summary>
        /// 更新查看次数
        /// </summary>
        /// <param name="id"></param>
        public void UpdateClickCount(int id)
        {
            if (id == 0) return;
            var strSql = new StringBuilder();
            strSql.Append("update Product set ");
            strSql.Append("[click] = [click]+1 ");
            strSql.Append("where [id] = @id;");
            SqlParameter[] parameters = {
                                            new SqlParameter("@id", SqlDbType.Int)
                                        };
            parameters[0].Value = id;
            DbHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <returns></returns>
        public int GetTotalCount()
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(id) from Product ");
            strSql.Append("where DeleteFlag = 0;");
            var result = DbHelper.ExecuteScalar(CommandType.Text, strSql.ToString());
            return result == null ? 0 : Convert.ToInt32(result);
        }

        #endregion

        #region BuildNavMenuObject

        /// <summary>
        /// 从DataReader中构建对象
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private ProductInfo DataReaderParser(IDataReader reader)
        {
            var product = new ProductInfo
                              {
                                  ID = reader.GetInt32("id"),
                                  Name = reader.GetString("name"),
                                  Descr = reader.GetString("descr"),
                                  Detail = reader.GetString("detail"),
                                  Picture = reader.GetString("picture"),
                                  Sort = reader.GetInt32("sort"),
                                  Click = reader.GetInt32("click")
                              };
            return product;
        }

        #endregion
    }
}

